package com.hsnn.medstgmini.base.dict.dao.impl;

import java.io.OutputStream;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.hsnn.medstgmini.base.dict.dao.DicDrugspecDao;
import com.hsnn.medstgmini.base.dict.model.DicDrugspec;
import com.hsnn.medstgmini.dao.HibernateSqlUtil;
import com.hsnn.medstgmini.util.Pagination;

/**
 * @category 基础库 数据字典 规格字典 接口 实现类
 * @author 韩守松
 * @date 2015年6月24日
 */
@Repository
public class DicDrugspecDaoImpl extends BaseHibernateDAO implements
		DicDrugspecDao {
	
	private static final Logger log = Logger.getLogger(DicDrugspecDaoImpl.class);

	@Autowired
	private HibernateSqlUtil dao;
	

	/**
	 * 基础库 数据字典 规格字典 查询
	 * 
	 * @param dicDrugspec
	 */
	@Override
	public Pagination queryDicDrugspecList(Pagination page, DicDrugspec dicDrugspec) {
		String specCode = dicDrugspec.getSpecCode();
		String specName = dicDrugspec.getSpecName();
		String status = null;
		if (dicDrugspec.getStatus() != null ) {
			status = dicDrugspec.getStatus()+"";
		}
		
		StringBuffer sql = new StringBuffer();
		sql.append("	      FROM                                              ")
			.append("		(SELECT b.spec_id,a.spec_code father_spec_code,     ")
			.append("	    a.spec_name father_spec_name,b.spec_code,           ")
			.append("        b.spec_name,b.status FROM dic_drugspec a           ")
			.append("	    INNER JOIN dic_drugspec b                           ")
			.append("		 ON a.spec_id=b.father_id                           ")
			.append("		UNION                                               ")
			.append("		SELECT spec_id,spec_code father_spec_code,          ")
			.append("       spec_name father_spec_name,NULL,NULL,STATUS         ")
			.append("       FROM dic_drugspec WHERE father_id='0')t where 1=1   ");
		
		if (StringUtils.isNotBlank(specCode)) {
			sql.append(" and (t.spec_code = :specCode or t.father_spec_code =:specCode) ");
		}
		if (StringUtils.isNotBlank(specName)) {
			sql.append(" and (t.spec_name like :specName or t.father_spec_name like :specName)  ");
		}
		if (StringUtils.isNotBlank(status)) {
			sql.append(" and (t.status = :status ) ");
		}
		StringBuffer hql = new StringBuffer();
		StringBuffer hqlCount = new StringBuffer();
		hql.append("select * ")
			.append(sql.toString());
		hqlCount.append("select count(*) ")
				.append(sql.toString());
		hql.append(" order by t.father_spec_code asc,t.spec_code asc ");
		Query query = getSession().createSQLQuery(hql.toString());
		Query queryCount = getSession().createSQLQuery(hqlCount.toString());
		if (StringUtils.isNotBlank(specCode)) {
			query.setParameter("specCode",  specCode );
			queryCount.setParameter("specCode", specCode);
		}
		if (StringUtils.isNotBlank(specName)) {
			query.setParameter("specName", "%" + specName + "%");
			queryCount.setParameter("specName", "%" + specName + "%");
		}
		if (StringUtils.isNotBlank(status)) {
			query.setParameter("status",  status );
			queryCount.setParameter("status", status);
		}
		//优化获取返回集合的条数yxc
		page.setRecords(Long.parseLong(queryCount.uniqueResult().toString()));
		//dicDrugspec.setCount(Long.valueOf(queryCount.list().get(0)+""));
		query.setFirstResult((page.getPage()-1)*page.getMaxResults());
		query.setMaxResults(page.getMaxResults());
		page.setRows(query.list());
		return page;
	}	

	/**
	 * @category 查询父级规格
	 * @author 韩守松
	 * @date 2015年6月25日
	 * @param @param FatherId
	 * @param @return
	 */
	@SuppressWarnings("unchecked")
	public List<DicDrugspec> getDicDrugspecListByFatherId(String FatherId) {
		StringBuffer hql = new StringBuffer();
		hql.append(" select dd.spec_id as fatherId, dd.father_id as fatherId2 from dic_drugspec dd where 1=1 ");
		if (StringUtils.isNotEmpty(FatherId) && !FatherId.equals("0")) {
			hql.append(" and dd.specId = :FatherId ");
		}
		if (StringUtils.isNotEmpty(FatherId) && FatherId.equals("0")) {
			hql.append(" and dd.fatherId = :FatherId2 ");
		}
		Query query = getSession().createQuery(hql.toString());
		if (StringUtils.isNotEmpty(FatherId) && !FatherId.equals("0")) {
			query.setParameter("FatherId", FatherId);
		}
		if (StringUtils.isNotEmpty(FatherId) && FatherId.equals("0")) {
			query.setParameter("FatherId2", FatherId);
		}
		return query.list();
	}
	
	/**
	 * 
	 *@category 启用和停用
	 *@author wangbing
	 *@date 2015年6月9日
	 *Pagination
	 */
	@Override
	public void changeStatus(DicDrugspec dicDrugspec) {
		Session session = getSession();
		StringBuffer sql = new StringBuffer();
		sql.append("update dic_drugspec  set ")
		  .append(" status=:status,upd_datetime=:updDatetime,upd_user=:updUser ")
		  .append(" where spec_id= :specId ");
		if("0".equals(dicDrugspec.getStatus()+"")){
			sql.append(" or father_id=:specId ");
		}
		Query update = session.createSQLQuery(sql.toString());
		update.setLong("status",dicDrugspec.getStatus());
		update.setTimestamp("updDatetime", new Date());
		update.setString("updUser", dicDrugspec.getUpdUser());
		update.setString("specId", dicDrugspec.getSpecId());
		update.executeUpdate();
		session.flush();
		String msg = "";
		if (1 == dicDrugspec.getStatus()){
			msg += "状态由停用变为启用";
		}
		else if (0 == dicDrugspec.getStatus()){
			msg += "状态由启用变为停用";
		}
		writeLog(dicDrugspec,msg);
	}
	/**
	 * 
	 *@category 写日志
	 *@author wangbing
	 *@date 2015年6月9日
	 *Pagination
	 */
	@Override
	public void writeLog(DicDrugspec dicDrugspec,String msg){
		Session session = getSession();
		StringBuffer sql = new StringBuffer();
		sql.append("insert into dic_drugspec_log(log_id,spec_id,spec_code,spec_name,  ")
		   .append("father_id,status,upd_datetime,upd_user,sort_id,detail) select ")
		   .append(" UUID(),t.*,:msg from dic_drugspec t where t.spec_id= :specId ");
		if("0".equals(dicDrugspec.getStatus()+"")){
			sql.append(" or t.father_id= :specId ");
		}
		Query query=session.createSQLQuery(sql.toString());
		query.setString("specId",dicDrugspec.getSpecId());
		query.setString("msg", msg);
		query.executeUpdate();
		session.flush();
		
	}
	
	@Override
	public Pagination initEdit(Pagination page){
		String specId = page.getConditions().get("specId");
		Session session = getSession();
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT b.spec_code fatherSpecCode,b.spec_name fatherSpecName,a.spec_code specCode,a.spec_name  specName FROM dic_drugspec a ")
		   .append(" LEFT JOIN dic_drugspec b ON a.father_id = b.spec_id WHERE a.spec_id =:specId ");
		Query query=session.createSQLQuery(sql.toString());
		query.setString("specId",specId);
		page.setRows(query.uniqueResult());
		return page;
	}
	/**
	 * 规格编辑
	 */
	@Override
	public void updateSpec(Pagination page) {
		String specId = page.getConditions().get("specId");
		String msg = compareData(specId,page);
		if (StringUtils.isNotBlank(msg)){
		String specCode = page.getConditions().get("specCode");
		String specName = page.getConditions().get("specName");
		String updUser = page.getConditions().get("updUser");
		StringBuffer sql = new StringBuffer();
		sql.append(" update dic_drugspec set spec_code=:specCode, ")
		   .append("spec_name=:specName,upd_datetime=:updDatetime,")
		   .append(" upd_user=:updUser where spec_id=:specId      ");
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("specId", specId);
		params.put("specName",specName );
		params.put("specCode",specCode );
		params.put("updUser",updUser );
		params.put("updDatetime", new Date());
		dao.executeSql(sql.toString(), params);
		String logsql = " insert into dic_drugspec_log(log_id,spec_id,spec_code,spec_name,father_id,status,upd_datetime,upd_user,sort_id,detail) select UUID(),t.*,:msg from dic_drugspec t where t.spec_id= :specId  ";
		params.put("msg", msg);
		params.remove("specCode");
		params.remove("specName");
		params.remove("updUser");
		params.remove("updDatetime");
		dao.executeSql(logsql, params);
		}
	}
	private String compareData(String id, Pagination page) {
		String sql = "select * from dic_drugspec where spec_id = :specId";
		Map<String, Object> params = new HashMap<String, Object>();
		String specId = page.getConditions().get("specId");
		params.put("specId", specId);
		@SuppressWarnings("unchecked")
		List<Map<String, Object>> list = (List<Map<String, Object>>) dao.getSqlMapList(sql, params);
		if (null != list && list.size() > 0){
			Map<String, Object> map = list.get(0);
			String specCode = page.getConditions().get("specCode");
			String specName = page.getConditions().get("specName");
			StringBuffer msg = new StringBuffer();
			if (! specCode.equals(map.get("spec_code").toString())){
				msg.append("规格编码由")
				.append(map.get("spec_code").toString())
				.append("变为")
				.append(specCode)
				.append("<br/>");
			}
			if (! specName.equals(map.get("spec_name").toString())){
				msg.append("规格名称由")
				.append(map.get("spec_name").toString())
				.append("变为")
				.append(specName)
				.append("<br/>");
			}
			return msg.toString();
		}
		return null;
	}

	/**
	 * 规格新增
	 */
	@Override
	public void addSpec(Pagination page) {
		String specCode = page.getConditions().get("specCode");
		String specName = page.getConditions().get("specName");
		String updUser = page.getConditions().get("updUser");
		String fatherId = page.getConditions().get("fatherId");
		String specId = page.getConditions().get("specAddId");
		StringBuffer sql = new StringBuffer();
		sql.append("insert into dic_drugspec(spec_id,spec_code,spec_name,father_id,  ")
		   .append("status,upd_datetime,upd_user) values ( ")
		   .append(":specId,:specCode,:specName,:fatherId,1,:updDatetime,:updUser) ");
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("specId", specId);
		params.put("specCode", specCode);
		params.put("specName", specName);
		params.put("updUser", updUser);
		params.put("updDatetime", new Date());
		params.put("fatherId", fatherId);
		dao.executeSql(sql.toString(), params);
		String logsql = " insert into dic_drugspec_log(log_id,spec_id,spec_code,spec_name,father_id,status,upd_datetime,upd_user,sort_id,detail) select UUID(),t.*,:msg from dic_drugspec t where t.spec_id= :specId  ";
		params.put("msg", "新增规格");
		params.remove("specCode");
		params.remove("specName");
		params.remove("updUser");
		params.remove("updDatetime");
		params.remove("fatherId");
		dao.executeSql(logsql, params);
	}
	
	/**
	 * 根据code或name查看有无重复数据
	 * @param code,name
	 * @return
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<DicDrugspec> getSpecBy(String key,String value,String specId) {
		StringBuffer sql = new StringBuffer();
		sql.append("select * from dic_drugspec where ")
		   .append(key)
		   .append(" = :value and  spec_id <> :specId ");
		if("spec_name".equals(key)){
			//规格名称可以和自己的一级或二级相同
			sql.append(" and spec_id not in (select spec_id from dic_drugspec where father_id = :specId ) ");
			sql.append(" and spec_id not in (select father_id from dic_drugspec where spec_id = :specId ) ");
		}
		Query query = getSession().createSQLQuery(sql.toString());
		query.setParameter("value", value);
		query.setParameter("specId",specId);
		return query.list();
	}
	
	@Override
	public void exportExcel(Pagination page,OutputStream os){
        HSSFWorkbook wb = new HSSFWorkbook();  
        HSSFSheet sheet = wb.createSheet("规格");  
        HSSFRow row = sheet.createRow((int) 0);  
        HSSFCellStyle style = wb.createCellStyle();  
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); 
        
        HSSFCell cell = null;
        String[] headers = {"一级编码","一级名称","二级编码","二级名称","状态"};
        for(int i = 0;i<headers.length;i++){
        	 cell = row.createCell(i);
             cell.setCellValue(headers[i]);  
             cell.setCellStyle(style);  
        }
        
        List<Object> list = getSpecNoPage(page);
        for (int i = 0; i < list.size(); i++)  
        {  
            row = sheet.createRow((int) i + 1);  
            Object[] obj = (Object[]) list.get(i);  
            for(int j = 0;j<obj.length;j++){
            	row.createCell(j).setCellValue(obj[j]==null?"":obj[j].toString());  
            }
            
        }  
        try {
            wb.write(os);  
            os.close();  
            page.setSuccess(true);
        } catch (Exception e){  
           page.setSuccess(false);
           log.error("export failed.", e); 
        }        
	}
	
	@SuppressWarnings("unchecked")
	private List<Object> getSpecNoPage(Pagination page){
		String specCode = page.getConditions().get("specCode");
		String specName = page.getConditions().get("specName");
		String status = page.getConditions().get("status");
		StringBuffer sql = new StringBuffer();
		sql.append("	     select *     FROM                                  ")
			.append("		(SELECT a.spec_code father_spec_code,     ")
			.append("	    a.spec_name father_spec_name,b.spec_code,           ")
			.append("        b.spec_name,case b.status when 0 then '停用' else '启用' end status FROM dic_drugspec a           ")
			.append("	    INNER JOIN dic_drugspec b                           ")
			.append("		 ON a.spec_id=b.father_id                           ")
			.append("		UNION                                               ")
			.append("		SELECT spec_code father_spec_code,          ")
			.append("       spec_name father_spec_name,NULL,NULL,case STATUS when 0 then '停用' else '启用' end  status       ")
			.append("       FROM dic_drugspec WHERE father_id='0')t where 1=1   ");
		    
		if (StringUtils.isNotBlank(specCode)) {
			sql.append(" and (t.spec_code = :specCode or t.father_spec_code =:specCode) ");
		}
		if (StringUtils.isNotBlank(specName)) {
			sql.append(" and (t.spec_name like :specName or t.father_spec_name like :specName)  ");
		}
		if (StringUtils.isNotBlank(status)) {
			sql.append(" and t.status = :status  ");
		}
		sql.append("       order by t.father_spec_code asc,t.spec_code asc     ");
		Query query = getSession().createSQLQuery(sql.toString());
		if (StringUtils.isNotBlank(specCode)) {
			query.setParameter("specCode",  specCode );
		}
		if (StringUtils.isNotBlank(specName)) {
			query.setParameter("specName", "%" + specName + "%");
		}
		if (StringUtils.isNotBlank(status)) {
			query.setParameter("status", "0".equals(status)?"停用":"启用");
		}
		return query.list();
	}
}